package cn.com.elite;

import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;
import org.dom4j.Element;

import com.elitecrm.saas.service.EliteSAASException;

public class QueryInfoService {
	private static final Log log = LogFactory.getLog(QueryInfoService.class);
	
	public List getQueryList(DataSource dataSource,Document doc,String ep_id,String Staff_ID)throws Exception{
		Element TypeEle=(Element)doc.selectSingleNode("/Root/Type");
		if(TypeEle!=null){
			String Type=TypeEle.getText();
			if(Type!=null){
		        String W_ExamResultMain="W_ExamResultMain_"+ep_id;
		        String W_ExamMain="W_ExamMain_"+ep_id;
		        String W_ExamScheme="W_ExamScheme_"+ep_id;
				if(Type.equals("2")){
					Element SchemeIDEle=(Element)doc.selectSingleNode("/Root/SchemeID");
					String SchemeID=SchemeIDEle.getText();
					Element ExamMainIDEle=(Element)doc.selectSingleNode("/Root/ExamMainID");
					String ExamMainID=ExamMainIDEle.getText();
			        String sql="select FirstIn,LastIn,BeginTime,EndTime,ComputerName," +
	        		"ComputerIP,"+W_ExamResultMain+".ExamMainID,"+W_ExamResultMain+".MarkScore,"+W_ExamMain+".ExamMainName" +
	        		" from "+W_ExamResultMain+" left join " +
	        		""+W_ExamScheme+" on "+W_ExamScheme+".SchemeID="+W_ExamResultMain+".SchemeID " +
	        		"left join "+W_ExamMain+" on "+W_ExamScheme+".ExamMainID="+W_ExamMain+".ExamMainID " +
	        		"where "+W_ExamResultMain+".MarkScore is not null and Staff_ID=? and "+W_ExamResultMain+".SchemeID=? and "+W_ExamResultMain+".ExamMainID=? order by EndTime desc";
					int[] types=new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR};
					Object[] params=new Object[]{Staff_ID,SchemeID,ExamMainID};
			        List list=util.QueryPersonUseExecute(sql,types,params,dataSource);
			        return list;
				}else if(Type.equals("1")){
			        String sql="select FirstIn,LastIn,BeginTime,EndTime,ComputerName," +
	        		"ComputerIP,"+W_ExamResultMain+".ExamMainID,"+W_ExamResultMain+".MarkScore,"+W_ExamMain+".ExamMainName" +
	        		" from "+W_ExamResultMain+" left join " +
	        		""+W_ExamScheme+" on "+W_ExamScheme+".SchemeID="+W_ExamResultMain+".SchemeID " +
	        		"left join "+W_ExamMain+" on "+W_ExamScheme+".ExamMainID="+W_ExamMain+".ExamMainID " +
	        		"where "+W_ExamResultMain+".MarkScore is not null  and Staff_ID=? order by EndTime desc";
					int[] types=new int[]{Types.VARCHAR};
					Object[] params=new Object[]{Staff_ID};
			        List list=util.QueryPersonUseExecute(sql,types,params,dataSource);
			        return list;
				}else{
					throw new EliteSAASException(EliteSAASException.INVAILD_PARAMETER,"TypeError");
				}
			}else{
				throw new EliteSAASException(EliteSAASException.INVAILD_PARAMETER,"Type==null");
			}
		}else{
			throw new EliteSAASException(EliteSAASException.INVAILD_PARAMETER,"TypeEle==null");
		}		
		
	}
	public String queryInfoService(DataSource dataSource,String ep_id,String Staff_ID,Document doc)throws Exception{
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String ReturnXml="";
        List list=getQueryList(dataSource,doc,ep_id,Staff_ID);
		Document result=util.getDoc();
		Element root=result.addElement("R");
		root.addElement("Result").addText("1");
		if(list!=null&&list.size()>0){
			root.addElement("KsresultLEN").addText(list.size()+"");
			for(int i=0;i<list.size();i++){
				Map map=(Map)list.get(i);
	    		Element kslist=root.addElement("Ksresult");
	    		
	    		String ExamListID=(String)map.get("ExamMainID");
	    		log.debug("ExamMainID:"+ExamListID);
	    		kslist.addElement("ExamListID").addText(ExamListID);
	    		
	    		String ExamMainName=(String)map.get("ExamMainName");
	    		log.debug("ExamMainName:"+ExamMainName);
	    		kslist.addElement("ExamMainName").addText(ExamMainName);
	    		
	    		String  Score=Double.toString((Double)map.get("MarkScore"));
	    		log.debug("Score:"+Score);
	    		kslist.addElement("Score").addText(Score);
	    		
	    		String LastMarkGroup=(String)map.get("LastMarkGroup");
	    		log.debug("LastMarkGroup:"+LastMarkGroup);
	    		if(LastMarkGroup==null){LastMarkGroup="";}
	    		kslist.addElement("LastMarkGroup").addText(LastMarkGroup);
	    		
	    		String LastMarkBy=(String)map.get("LastMarkBy");
	    		log.debug("LastMarkBy:"+LastMarkBy);
	    		if(LastMarkBy==null){LastMarkBy="";}
	    		kslist.addElement("LastMarkBy").addText(LastMarkBy);
	    		
	    		String FirstIn=Integer.toString((Integer)map.get("FirstIn"));
	    		log.debug("FirstIn:"+FirstIn);
	    		kslist.addElement("FirstIn").addText(FirstIn);
	    		
	    		String LastIn=Integer.toString((Integer)map.get("LastIn"));
	    		log.debug("LastIn:"+LastIn);
	    		kslist.addElement("LastIn").addText(LastIn);
	    		
	    		Timestamp BeginTime1=(Timestamp)map.get("BeginTime");
	    		String BeginTime=df.format(BeginTime1);
	    		log.debug("BeginTime:"+BeginTime);
	    		kslist.addElement("BeginTime").addText(BeginTime);

	    		Timestamp EndTime1=(Timestamp)map.get("EndTime");
	    		String EndTime=df.format(EndTime1);
	    		log.debug("EndTime:"+EndTime);
	    		kslist.addElement("EndTime").addText(EndTime);
	    		
	    		String ComputerName=(String)map.get("ComputerName");
	    		log.debug("ComputerName:"+ComputerName);
	    		kslist.addElement("ComputerName").addText(ComputerName);
	    		
	    		String ComputerIP=(String)map.get("ComputerIP");
	    		log.debug("ComputerIP:"+ComputerIP);
	    		kslist.addElement("ComputerIP").addText(ComputerIP);
				
			}
		}
		ReturnXml=result.asXML();
		return ReturnXml;
	}

}
